drop table jms_dm.dm_sqs_worn_waybill_summary_dt;
CREATE TABLE jms_dm.dm_sqs_worn_waybill_summary_dt
(
    flag                 int(11) COMMENT '1直接根据责任网点进行统计2根据所属代理区进行统计0是不在工单里的网点标识',
    resp_network_id      varchar(100) COMMENT '责任网点id',
    resp_network_code    varchar(100) COMMENT '责任网点code',
    resp_network_name    varchar(100) COMMENT '责任网点name',
    duty_network_type    varchar(100) COMMENT '责任网点网点类型(基础资料)',
    duty_virt_code       varchar(100) COMMENT '责任网点虚拟代理区code(基础资料)',
    duty_virt_name       varchar(100) COMMENT '责任网点虚拟代理区name(基础资料)',
    duty_manage_code     varchar(100) COMMENT '责任网点管理大区(基础资料)',
    duty_manage_name     varchar(100) COMMENT '责任网点管理大区名称(基础资料)',
    duty_regional_id     varchar(100) COMMENT '责任网点大区ID(基础资料)',
    duty_regional_desc   varchar(100) COMMENT '责任网点大区name(基础资料)',
    duty_agent_code      varchar(100) COMMENT '责任网点代理区编码(基础资料)',
    duty_agent_name      varchar(100) COMMENT '责任网点代理区名称(基础资料)',
    duty_fran_code       varchar(100) COMMENT '责任网点加盟商编码(基础资料)',
    duty_fran_name       varchar(100) COMMENT '责任网点加盟商名称(基础资料)',
    duty_center_code     varchar(100) COMMENT '责任网点中心编码(基础资料)',
    duty_center_name     varchar(100) COMMENT '责任网点中心名称(基础资料)',
    duty_provider_id     varchar(100) COMMENT '责任网点省份ID(基础资料)',
    duty_provider_desc   varchar(100) COMMENT '责任网点省份(基础资料)',
    duty_city_id         varchar(100) COMMENT '责任网点城市ID(基础资料)',
    duty_city_desc       varchar(100) COMMENT '责任网点城市(基础资料)',
    duty_area_id         varchar(100) COMMENT '责任网点区/县id(基础资料)',
    duty_area_desc       varchar(100) COMMENT '责任网点区/县(基础资料)',
    worn_num             decimal(16, 4) COMMENT '破损量',
    zhiliang_worn_num    decimal(16, 4) COMMENT '质量-破损票数',
    zhongcai_worn_num    decimal(16, 4) COMMENT '仲裁-破损票数',
    lipei_worn_num       decimal(16, 4) COMMENT '赔付-破损票数',
    zhiliang_goods_value decimal(24, 6) COMMENT '质量-罚款金额',
    zhongcai_goods_value decimal(24, 6) COMMENT '仲裁-罚款金额',
    lipei_goods_value    decimal(24, 6) COMMENT '赔付-罚款金额',
    goods_value          decimal(24, 6) COMMENT '总金额',
    tab_num              int(11) COMMENT '操作量',
    worn_rate            double COMMENT '破损票数÷操作量*1000000',
    remark1              varchar(100) COMMENT '备用',
    remark2              varchar(100) COMMENT '备用',
    remark3              varchar(100) COMMENT '备用',
    date_time            date COMMENT '日期',
    zone_id              varchar(100) COMMENT '片區id',
    zone_code            varchar(100) COMMENT '片區code',
    zone_name            varchar(100) COMMENT '片区name'
) ENGINE=OLAP
DUPLICATE KEY(flag, resp_network_id, resp_network_code)
COMMENT '破损率汇总'
PARTITION BY RANGE(date_time)
(START ('2023-01-01') END ('2023-04-26') EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(resp_network_id) BUCKETS 1
PROPERTIES (
'replication_num' = '3',
'dynamic_partition.enable' = 'true',
'dynamic_partition.time_unit' = 'DAY',
'dynamic_partition.time_zone' = 'Asia/Shanghai',
'dynamic_partition.start' = '-180',
'dynamic_partition.end' = '3',
'dynamic_partition.prefix' = 'p',
'dynamic_partition.buckets' = '1',
'in_memory' = 'false',
'storage_format' = 'V2'
);